﻿using System.Collections.Generic;
using System.Data;
using IceCS.GoodsSale.Models.UserModels;
using Microsoft.Data.SqlClient;

namespace IceCS.GoodsSale.DAL
{
    public class UserPurviewAccesser
    {
        DBHelper dh = new DBHelper();

        //查询用户权限信息的方法
        public UserPurview SelectUserPurview(string userName)
        {
            string sqlStr = "select * from TB_UserPurview where UserName='" + userName + "'";
            DataTable dt = dh.GetDataTable(sqlStr);
            UserPurview userPurview = new UserPurview();
            if (dt.Rows.Count != 0)
            {
                userPurview.UserName = dt.Rows[0].ItemArray[0].ToString();
                userPurview.UserManage = dt.Rows[0].ItemArray[1].ToString();
                userPurview.PurchaseGoods = dt.Rows[0].ItemArray[2].ToString();
                userPurview.GoodsQuery = dt.Rows[0].ItemArray[3].ToString();
                userPurview.GoodsSale = dt.Rows[0].ItemArray[4].ToString();
                userPurview.SaleDataQuery = dt.Rows[0].ItemArray[5].ToString();
                userPurview.SaleDataCount = dt.Rows[0].ItemArray[6].ToString();
                userPurview.InventoryAlarm = dt.Rows[0].ItemArray[7].ToString();
                userPurview.InventoryQuery = dt.Rows[0].ItemArray[8].ToString();
                userPurview.BackupData = dt.Rows[0].ItemArray[9].ToString();
                userPurview.RestoreData = dt.Rows[0].ItemArray[10].ToString();
                return userPurview;
            }
            else
            {
                return null;
            }
        }

        //多表连接查询类型为“一般用户”的用户权限信息的方法，返回用户权限信息列表
        public List<UserPurview> SelectUserPurviews()
        {
            string sqlStr = "select TB_User.UserName,TB_UserPurview.UserManage,TB_UserPurview.PurchaseGoods,TB_UserPurview.GoodsQuery,TB_UserPurview.GoodsSale,TB_UserPurview.SaleDataQuery,TB_UserPurview.SaleDataCount,TB_UserPurview.InventoryAlarm,TB_UserPurview.InventoryQuery,TB_UserPurview.BackupData,TB_UserPurview.RestoreData from TB_User inner join TB_UserPurview on TB_User.UserName = TB_UserPurview.UserName where UserType='一般用户'";
            DataTable dt = dh.GetDataTable(sqlStr);
            List<UserPurview> list = new List<UserPurview>();
            foreach (DataRow r in dt.Rows)
            {
                UserPurview userPurview = new UserPurview();
                userPurview.UserName = r["UserName"].ToString();
                userPurview.UserManage = r["UserManage"].ToString();
                userPurview.PurchaseGoods = r["PurchaseGoods"].ToString();
                userPurview.GoodsQuery = r["GoodsQuery"].ToString();
                userPurview.GoodsSale = r["GoodsSale"].ToString();
                userPurview.SaleDataQuery = r["SaleDataQuery"].ToString();
                userPurview.SaleDataCount = r["SaleDataCount"].ToString();
                userPurview.InventoryAlarm = r["InventoryAlarm"].ToString();
                userPurview.InventoryQuery = r["InventoryQuery"].ToString();
                userPurview.BackupData = r["BackupData"].ToString();
                userPurview.RestoreData = r["RestoreData"].ToString();
                list.Add(userPurview);
            }
            return list;
        }

        //修改用户权限的方法
        public bool UpdateUserPurview(UserPurview userPurview)
        {
            string sqlStr = "update TB_UserPurview set UserManage=@UserManage,PurchaseGoods=@PurchaseGoods,GoodsQuery=@GoodsQuery,GoodsSale=@GoodsSale,SaleDataQuery=@SaleDataQuery,SaleDataCount=@SaleDataCount,InventoryAlarm=@InventoryAlarm,InventoryQuery=@InventoryQuery,BackupData=@BackupData,RestoreData=@RestoreData where UserName=@UserName";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UserManage",userPurview.UserManage),
                new SqlParameter("@PurchaseGoods",userPurview.PurchaseGoods),
                new SqlParameter("@GoodsQuery",userPurview.GoodsQuery),
                new SqlParameter("@GoodsSale",userPurview.GoodsSale),
                new SqlParameter("@SaleDataQuery",userPurview.SaleDataQuery),
                new SqlParameter("@SaleDataCount",userPurview.SaleDataCount),
                new SqlParameter("@InventoryAlarm",userPurview.InventoryAlarm),
                new SqlParameter("@InventoryQuery",userPurview.InventoryQuery),
                new SqlParameter("@BackupData",userPurview.BackupData),
                new SqlParameter("@RestoreData",userPurview.RestoreData),
                new SqlParameter("@UserName",userPurview.UserName),
            };
            bool b = dh.ExcuteCommand(sqlStr, param);
            return b;
        }
    }
}
